"""
cron: 0 */6 * * *
new Env('房产市场分析专业版');
"""
import requests
import os
import mysql.connector
import json
import time
from datetime import datetime
from bs4 import BeautifulSoup

class HouseAnalysisPro:
    def __init__(self):
        self.connection = mysql.connector.connect(
            host=os.environ.get('HOST'),
            user=os.environ.get('USER'),
            password=os.environ.get('PASSWORD'),
            database=os.environ.get('DATABASE')
        )
        self.cursor = self.connection.cursor()
        self.create_tables()
        
    def create_tables(self):
        """创建必要的数据表"""
        # 房源信息表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_house_info (
                id INT AUTO_INCREMENT PRIMARY KEY,
                house_id VARCHAR(50),
                title VARCHAR(200),
                district VARCHAR(50),
                area VARCHAR(50),
                price DECIMAL(10,2),
                unit_price DECIMAL(10,2),
                room_type VARCHAR(50),
                floor VARCHAR(50),
                total_floor INT,
                decoration VARCHAR(50),
                build_year INT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_district (district),
                INDEX idx_price (price),
                INDEX idx_create_time (create_time)
            )
        """)
        
        # 市场分析表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_house_analysis (
                id INT AUTO_INCREMENT PRIMARY KEY,
                district VARCHAR(50),
                avg_price DECIMAL(10,2),
                price_trend DECIMAL(5,2),
                deal_count INT,
                new_count INT,
                inventory INT,
                turnover_rate DECIMAL(5,2),
                investment_rating INT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_district (district),
                INDEX idx_create_time (create_time)
            )
        """)
        
        # 价格预测表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_house_prediction (
                id INT AUTO_INCREMENT PRIMARY KEY,
                district VARCHAR(50),
                current_price DECIMAL(10,2),
                predict_1m DECIMAL(10,2),
                predict_3m DECIMAL(10,2),
                predict_6m DECIMAL(10,2),
                confidence DECIMAL(5,2),
                factors TEXT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_district (district)
            )
        """)

    def get_lianjia_data(self, city, district):
        """获取链家房源数据"""
        try:
            headers = {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'
            }
            
            url = f'https://{city}.lianjia.com/ershoufang/{district}/'
            response = requests.get(url, headers=headers)
            
            if response.status_code == 200:
                soup = BeautifulSoup(response.text, 'html.parser')
                house_list = soup.find_all('div', class_='info clear')
                
                for house in house_list:
                    # 提取房源信息
                    title = house.find('div', class_='title').text.strip()
                    house_id = house.find('a')['data-housecode']
                    price = float(house.find('div', class_='totalPrice').text.strip('万'))
                    unit_price = float(house.find('div', class_='unitPrice').text.strip('元/平米'))
                    
                    # 获取详细信息
                    detail_url = f'https://{city}.lianjia.com/ershoufang/{house_id}.html'
                    detail_response = requests.get(detail_url, headers=headers)
                    detail_soup = BeautifulSoup(detail_response.text, 'html.parser')
                    
                    # 解析详细信息
                    house_data = {
                        'house_id': house_id,
                        'title': title,
                        'district': district,
                        'price': price,
                        'unit_price': unit_price,
                        'room_type': detail_soup.find('div', class_='room').text.strip(),
                        'floor': detail_soup.find('div', class_='floor').text.strip(),
                        'total_floor': int(detail_soup.find('div', class_='floor').text.split('/')[1].strip('层')),
                        'decoration': detail_soup.find('div', class_='decoration').text.strip(),
                        'build_year': int(detail_soup.find('div', class_='year').text.strip('年'))
                    }
                    
                    self.save_house_data(house_data)
                    time.sleep(1)  # 避免请求过快
                    
        except Exception as e:
            print(f"获取链家数据失败: {str(e)}")

    def analyze_market(self, district):
        """分析市场情况"""
        try:
            # 计算均价和价格趋势
            query = """
                SELECT 
                    AVG(unit_price) as avg_price,
                    COUNT(*) as total_count,
                    SUM(CASE WHEN create_time > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as new_count
                FROM ningm_house_info 
                WHERE district = %s
            """
            self.cursor.execute(query, (district,))
            result = self.cursor.fetchone()
            
            # 计算价格趋势
            trend_query = """
                SELECT AVG(unit_price) 
                FROM ningm_house_info 
                WHERE district = %s 
                AND create_time < DATE_SUB(NOW(), INTERVAL 30 DAY)
            """
            self.cursor.execute(trend_query, (district,))
            last_month_price = self.cursor.fetchone()[0]
            
            current_price = result[0]
            price_trend = ((current_price - last_month_price) / last_month_price * 100) if last_month_price else 0
            
            # 计算投资评级（1-5星）
            investment_rating = self.calculate_investment_rating(
                price_trend,
                result[1],  # total_count
                result[2]   # new_count
            )
            
            # 保存分析结果
            insert_query = """
                INSERT INTO ningm_house_analysis 
                (district, avg_price, price_trend, deal_count, new_count, 
                 inventory, turnover_rate, investment_rating) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            """
            insert_values = (
                district,
                current_price,
                price_trend,
                result[1],
                result[2],
                result[1] - result[2],  # inventory
                (result[2] / result[1] * 100) if result[1] > 0 else 0,  # turnover_rate
                investment_rating
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"市场分析失败: {str(e)}")
            self.connection.rollback()

    def predict_price(self, district):
        """预测房价走势"""
        try:
            # 获取历史数据
            query = """
                SELECT AVG(unit_price) as avg_price, DATE(create_time) as date
                FROM ningm_house_info 
                WHERE district = %s 
                GROUP BY DATE(create_time)
                ORDER BY date DESC
                LIMIT 90
            """
            self.cursor.execute(query, (district,))
            historical_data = self.cursor.fetchall()
            
            if len(historical_data) < 30:
                return
            
            # 简单线性回归预测
            current_price = historical_data[0][0]
            price_change = (historical_data[0][0] - historical_data[-1][0]) / len(historical_data)
            
            predict_1m = current_price + (price_change * 30)
            predict_3m = current_price + (price_change * 90)
            predict_6m = current_price + (price_change * 180)
            
            # 影响因素分析
            factors = {
                'price_trend': price_change > 0,
                'market_activity': len(historical_data) > 60,
                'seasonal_factor': datetime.now().month in [3,4,5,9,10,11],  # 传统旺季
                'policy_impact': True  # 需要根据实际政策调整
            }
            
            # 计算置信度
            confidence = self.calculate_confidence(factors)
            
            # 保存预测结果
            insert_query = """
                INSERT INTO ningm_house_prediction 
                (district, current_price, predict_1m, predict_3m, predict_6m, 
                 confidence, factors) 
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            insert_values = (
                district,
                current_price,
                predict_1m,
                predict_3m,
                predict_6m,
                confidence,
                json.dumps(factors)
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"价格预测失败: {str(e)}")
            self.connection.rollback()

    def calculate_investment_rating(self, price_trend, total_count, new_count):
        """计算投资评级"""
        score = 0
        
        # 价格趋势评分
        if price_trend > 10:
            score += 2
        elif price_trend > 5:
            score += 1.5
        elif price_trend > 0:
            score += 1
            
        # 市场活跃度评分
        activity_rate = new_count / total_count if total_count > 0 else 0
        if activity_rate > 0.3:
            score += 2
        elif activity_rate > 0.2:
            score += 1.5
        elif activity_rate > 0.1:
            score += 1
            
        # 库存压力评分（越低越好）
        inventory_pressure = (total_count - new_count) / total_count if total_count > 0 else 1
        if inventory_pressure < 0.3:
            score += 1
        elif inventory_pressure < 0.5:
            score += 0.5
            
        return min(int(score), 5)  # 最高5星

    def calculate_confidence(self, factors):
        """计算预测置信度"""
        confidence = 0
        weights = {
            'price_trend': 0.3,
            'market_activity': 0.2,
            'seasonal_factor': 0.2,
            'policy_impact': 0.3
        }
        
        for factor, value in factors.items():
            if value:
                confidence += weights[factor] * 100
                
        return min(confidence, 100)

    def save_house_data(self, house_data):
        """保存房源数据"""
        try:
            insert_query = """
                INSERT INTO ningm_house_info 
                (house_id, title, district, price, unit_price, room_type, 
                 floor, total_floor, decoration, build_year) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            insert_values = (
                house_data['house_id'],
                house_data['title'],
                house_data['district'],
                house_data['price'],
                house_data['unit_price'],
                house_data['room_type'],
                house_data['floor'],
                house_data['total_floor'],
                house_data['decoration'],
                house_data['build_year']
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"保存房源数据失败: {str(e)}")
            self.connection.rollback()

    def run(self):
        """运行分析系统"""
        try:
            # 监测城市和区域列表
            city_districts = {
                'bj': ['chaoyang', 'haidian', 'dongcheng', 'xicheng'],
                'sh': ['pudong', 'minhang', 'xuhui', 'putuo'],
                'gz': ['tianhe', 'yuexiu', 'haizhu', 'panyu'],
                'sz': ['futian', 'nanshan', 'luohu', 'baoan']
            }
            
            for city, districts in city_districts.items():
                for district in districts:
                    # 1. 获取房源数据
                    self.get_lianjia_data(city, district)
                    
                    # 2. 分析市场情况
                    self.analyze_market(district)
                    
                    # 3. 预测价格走势
                    self.predict_price(district)
                    
                    time.sleep(5)  # 避免请求过快
            
        except Exception as e:
            print(f"系统运行失败: {str(e)}")
        finally:
            self.cursor.close()
            self.connection.close()

if __name__ == "__main__":
    analyzer = HouseAnalysisPro()
    analyzer.run() 